<?php

/*Klasa odpowiedzialna za przetwarzanie obecnosci uczniow danej klasy*/
class Attendances extends Controller
{
	/*Konstruktor klasy*/
	function Attendances()
	{
		parent::Controller();
		$this->load->helper('url');
        $this->load->helper('form');
        $this->load->helper('email');
	}
	
	/*Metoda ladowana automatycznie*/
	function index()
	{
		$this->load->helper('form');		
	}
	
	/*Metoda przygotowujaca dane do wyswietlania obecnosci na witrynie www*/
	function obecnosci()
	{
	
		$przedmiot = $this->input->post('przedmiot');
		$klasa = $this->input->post('klasa');
		$imie = $this->input->post('imie');
		$nazwisko = $this->input->post('nazwisko');
		
		/*Obliczanie liczby uczniow w danej klasie*/
		$query = "SELECT COUNT(s.id) AS luczniow FROM students s, classes c WHERE s.classe_id = c.id AND c.name = '" . $klasa ."'";
		$query = $this->db->query($query);
		
		foreach ($query->result() as $row)
    	{
    		$luczniow = $row->luczniow;
        }
        
        
        /*Przygotowanie listy uczniow danej klasy*/
        $query = "SELECT s.firstname AS imie, s.secondname AS nazwisko FROM classes c, students s, attendances a, subjects su WHERE c.id = s.classe_id 
        			AND s.id = a.student_id AND su.id = a.subject_id AND c.name = '" . $klasa ."' AND su.name = '" . $przedmiot ."' GROUP BY c.id";
		$query = $this->db->query($query);
		
		
		/*Zmienna opisujaca czy do danej klasy sa zapisani uczniowie*/
		$flaga = 0;
		
		
		/*Pobranie ucznia ze sprawdzanej klasy*/
		foreach ($query->result() as $row)
    	{
    		$imieucznia = $row->imie;
    		$nazwiskoucznia = $row->nazwisko;
    		$flaga = 1;
        }
        
        
        /*Jesli uczniowie wystepuja w danej klasie*/
        if($flaga == 1)
        {
        	/*Pobranie ilosci lekcji (obecnosci) jakie posiadaja uczniowie danej klasy*/
	        $query = "SELECT COUNT(*) AS ilosc FROM classes c, students s, attendances a, subjects su WHERE c.id = s.classe_id 
        				AND s.id = a.student_id AND su.id = a.subject_id AND c.name = '" . $klasa ."' AND su.name = '" . $przedmiot ."'
      					AND s.firstname = '" . $imieucznia . "' AND s.secondname = '" . $nazwiskoucznia ."'";
			$query = $this->db->query($query);
		
			/*Ustawienie zmiennej, w rzypadku, gdy w jednym dniu wstawiane jes kilka obecnosci z jednego przemiotu (wieksza liczba zajec)
			inkrementacja numeru lekcji wlasnie sprawdzanego*/
			foreach ($query->result() as $row)
    		{
    			$iloscobecnosci = $row->ilosc;
        	}
    	}
    	else
    	{
	    	$iloscobecnosci = 0;
    	}
        
    	/*Perzygotowanie listy obecnosci uczniow*/
        $query = "SELECT s.firstname AS imie, s.secondname AS nazwisko, a.dayy AS dzien, a.monthh AS miesiac, a.yearr AS rok, a.lesson AS godzina, a.attendance AS obecnosc
        FROM classes c, students s, attendances a, subjects su WHERE c.id = s.classe_id AND s.id = a.student_id AND su.id = a.subject_id AND c.name = '" . $klasa ."' 
        AND su.name = '" . $przedmiot . "' ORDER BY s.secondname, s.firstname, rok, miesiac, dzien, godzina";    
        $obecnosci = $this->db->query($query);
        
        /*Przygotowanie dat, w ktorych sprawdzane byly obecnosci*/
        $query  = "SELECT a.dayy AS dzien, a.monthh AS miesiac, a.yearr AS rok, a.lesson AS godzina, a.attendance AS obecnosc
        		FROM classes c, students s, attendances a, subjects su WHERE c.id = s.classe_id AND s.id = a.student_id AND su.id = a.subject_id AND c.name = '" . $klasa ."' 
        		AND su.name = '" . $przedmiot . "' GROUP BY rok, miesiac, dzien, godzina";   
        $daty = $this->db->query($query);
        
        
        /*Wykonanie spisu uczniow danej klasy*/
        $query = "SELECT s.id AS id, s.firstname AS imie, s.secondname AS nazwisko FROM students s, classes c WHERE s.classe_id = c.id AND c.name = '" . $klasa . "' ORDER BY nazwisko, imie";       
        $spisuczniow = $this->db->query($query);
        
        /*Tablica do wysylania danych do widoku*/
        $tablica = array('imie' => $imie, 'nazwisko' => $nazwisko, 'klasa' => $klasa, 'przedmiot' => $przedmiot, 'luczniow' => $luczniow, 'iloscobecnosci' => $iloscobecnosci,
        			'obecnosci' => $obecnosci, 'daty' => $daty, 'spisuczniow' => $spisuczniow);
        
       	/*Zaladowanie widoku*/
        $this->load->view("attendances_view", $tablica);
        
	}
	
	function sprawdzanieobecnosci($przedmiot, $klasa, $imie, $nazwisko)
	{
	
		if(NULL ==  getenv('HTTP_REFERER'))
		{
			echo "NASTĄPIŁA PRÓBA WYŁUDZENIA INFORMACJI !";
		}
		else
		{
		
			$query = "SELECT COUNT(s.id) AS luczniow FROM students s, classes c WHERE s.classe_id = c.id AND c.name = '" . $klasa ."'";
			$query = $this->db->query($query);
		
			foreach ($query->result() as $row)
    		{
    			$luczniow = $row->luczniow;
        	}
        
        
        	$query = "SELECT s.firstname AS imie, s.secondname AS nazwisko FROM classes c, students s, attendances a, subjects su WHERE c.id = s.classe_id 
        				AND s.id = a.student_id AND su.id = a.subject_id AND c.name = '" . $klasa ."' AND su.name = '" . $przedmiot ."' GROUP BY
						c.id";
			$query = $this->db->query($query);
		
		
			$flaga = 0;
		
		
			foreach ($query->result() as $row)
    		{
    			$imieucznia = $row->imie;
    			$nazwiskoucznia = $row->nazwisko;
    			$flaga = 1;
        	}
        
        	if($flaga == 1)
        	{
        		$query = "SELECT COUNT(*) AS ilosc FROM classes c, students s, attendances a, subjects su WHERE c.id = s.classe_id 
        					AND s.id = a.student_id AND su.id = a.subject_id AND c.name = '" . $klasa ."' AND su.name = '" . $przedmiot ."'
      						AND s.firstname = '" . $imieucznia . "' AND s.secondname = '" . $nazwiskoucznia ."'";
 
				$query = $this->db->query($query);
		
				foreach ($query->result() as $row)
    			{
    				$iloscobecnosci = $row->ilosc;
        		}	
    		}
    		else
    		{
	    		$iloscobecnosci = 0;
    		}
        
    	
        	$query = "SELECT s.firstname AS imie, s.secondname AS nazwisko, a.dayy AS dzien, a.monthh AS miesiac, a.yearr AS rok, a.lesson AS godzina, a.attendance AS obecnosc
        	FROM classes c, students s, attendances a, subjects su WHERE c.id = s.classe_id AND s.id = a.student_id AND su.id = a.subject_id AND c.name = '" . $klasa ."' 
        	AND su.name = '" . $przedmiot . "' ORDER BY s.secondname, s.firstname, rok, miesiac, dzien, godzina";     
        	$obecnosci = $this->db->query($query);
        
        
        	$query  = "SELECT a.dayy AS dzien, a.monthh AS miesiac, a.yearr AS rok, a.lesson AS godzina, a.attendance AS obecnosc
        			FROM classes c, students s, attendances a, subjects su WHERE c.id = s.classe_id AND s.id = a.student_id AND su.id = a.subject_id AND c.name = '" . $klasa ."' 
        			AND su.name = '" . $przedmiot . "' GROUP BY rok, miesiac, dzien, godzina";   
        	$daty = $this->db->query($query);
        
        
        	$query = "SELECT s.id AS id, s.firstname AS imie, s.secondname AS nazwisko FROM students s, classes c WHERE s.classe_id = c.id AND c.name = '" . $klasa . "' ORDER BY nazwisko, imie";
        	$spisuczniow = $this->db->query($query);
        
        
        	$tablica = array('imie' => $imie, 'nazwisko' => $nazwisko, 'klasa' => $klasa, 'przedmiot' => $przedmiot, 'luczniow' => $luczniow, 'iloscobecnosci' => $iloscobecnosci,
        	'obecnosci' => $obecnosci, 'daty' => $daty, 'spisuczniow' => $spisuczniow);
        	$this->load->view("attendances_view_add", $tablica);
        
		}
	}
	
	
	
	function wstawobecnosci()
	{
		$obecnosc = $this->input->post('obecnosc');
		
		if($obecnosc != NULL)
		{
			$data = getdate();
			$dzien = $data['mday'];
			$miesiac = $data['mon'];
			$rok = $data['year'];
			
			$klasa = $this->input->post('class');
			$przedmiot = $this->input->post('subject');
			$imie = $this->input->post('imie');
			$nazwisko = $this->input->post('nazwisko');
			
			$query = "SELECT s.id AS id FROM students s, classes c WHERE s.classe_id = c.id AND c.name = '" . $klasa . "' GROUP BY c.id";
			$query = $this->db->query($query);
			
					
			foreach ($query->result() as $row)
    		{	
    			$iducznia = $row->id;
        	}
        	
        	
        	$query =   "SELECT COUNT(*) as ilosc FROM attendances a, subjects s WHERE a.subject_id = s.id AND s.name = '" . $przedmiot . "' AND a.student_id = " . $iducznia . " AND dayy = " . $dzien . " AND monthh = " . $miesiac . " AND yearr = " . $rok;
        	$query = $this->db->query($query);
        	
        	foreach ($query->result() as $row)
    		{	
    			$ilosclekcji = $row->ilosc;
        	}
        	
        	        	
        	$ilosclekcji ++;
        	
        	$query = "SELECT s.id AS id FROM students s, classes c WHERE s.classe_id = c.id AND c.name ='" . $klasa . "' ORDER BY id";
        	$spisuczniow = $this->db->query($query);
        	
        	$query = "SELECT id FROM subjects WHERE name = '" . $przedmiot ."'";
        	$query = $this->db->query($query);
			
        	foreach ($query->result() as $row)
    		{	
    			$idprzedmiotu = $row->id;
        	}
        	
        	
        	foreach ($spisuczniow->result() as $row)
    		{	
    			$flaga = 0;
	    		foreach($obecnosc as $val)
 				{
 					
	 				if($val == $row -> id)
 					{
	 					$query = "insert into attendances (dayy, monthh, yearr, student_id, subject_id, attendance, lesson) values (" . $dzien . ", " . $miesiac .
	 					", " . $rok . ", " . $row->id . ", " . $idprzedmiotu . ", 1, " . $ilosclekcji . ")";
	 					$query = $this->db->query($query);
	 					$flaga = 1;
 					}
 					
 				}
 				if($flaga == 0)
 				{
	 				$query = "insert into attendances (dayy, monthh, yearr, student_id, subject_id, attendance, lesson) values (" . $dzien . ", " . $miesiac .
	 				", " . $rok . ", " . $row->id . ", " . $idprzedmiotu . ", 0, " . $ilosclekcji . ")";
	 				$query = $this->db->query($query);
 				}
        	}
        	
        	$query = "SELECT COUNT(s.id) AS luczniow FROM students s, classes c WHERE s.classe_id = c.id AND c.name = '" . $klasa ."'";
			$query = $this->db->query($query);
		
			foreach ($query->result() as $row)
    		{
    			$luczniow = $row->luczniow;
        	}
        
        	$query = "SELECT s.firstname AS imie, s.secondname AS nazwisko FROM classes c, students s, attendances a, subjects su WHERE c.id = s.classe_id 
        			AND s.id = a.student_id AND su.id = a.subject_id AND c.name = '" . $klasa ."' AND su.name = '" . $przedmiot ."' GROUP BY c.id";
			$query = $this->db->query($query);
		
			$flaga = 0;
		
			foreach ($query->result() as $row)
    		{
    			$imieucznia = $row->imie;
    			$nazwiskoucznia = $row->nazwisko;
    			$flaga = 1;
        	}
        
        	if($flaga == 1)
        	{
        		$query = "SELECT COUNT(*) AS ilosc FROM classes c, students s, attendances a, subjects su WHERE c.id = s.classe_id 
        				AND s.id = a.student_id AND su.id = a.subject_id AND c.name = '" . $klasa ."' AND su.name = '" . $przedmiot ."'
      					AND s.firstname = '" . $imieucznia . "' AND s.secondname = '" . $nazwiskoucznia ."'";
 
				$query = $this->db->query($query);
		
				foreach ($query->result() as $row)
    			{
    				$iloscobecnosci = $row->ilosc;
        		}
    		}
    		else
    		{
	    		$iloscobecnosci = 0;
    		}
        
        	$query = "SELECT s.firstname AS imie, s.secondname AS nazwisko, a.dayy AS dzien, a.monthh AS miesiac, a.yearr AS rok, a.lesson AS godzina, a.attendance AS obecnosc
        			FROM classes c, students s, attendances a, subjects su WHERE c.id = s.classe_id AND s.id = a.student_id AND su.id = a.subject_id AND c.name = '" . $klasa ."' 
        			AND su.name = '" . $przedmiot . "' ORDER BY s.secondname, s.firstname, rok, miesiac, dzien, godzina";
        
        	$obecnosci = $this->db->query($query);
        
        	$query  = "SELECT a.dayy AS dzien, a.monthh AS miesiac, a.yearr AS rok, a.lesson AS godzina, a.attendance AS obecnosc
        				FROM classes c, students s, attendances a, subjects su WHERE c.id = s.classe_id AND s.id = a.student_id AND su.id = a.subject_id AND c.name = '" . $klasa ."' 
        				AND su.name = '" . $przedmiot . "' GROUP BY rok, miesiac, dzien, godzina";
        
        	$daty = $this->db->query($query);
        
        	$query = "SELECT s.id AS id, s.firstname AS imie, s.secondname AS nazwisko FROM students s, classes c WHERE s.classe_id = c.id AND c.name = '" . $klasa . "' ORDER BY nazwisko, imie";
        
        	$spisuczniow = $this->db->query($query);
        
        	$tablica = array('imie' => $imie, 'nazwisko' => $nazwisko, 'klasa' => $klasa, 'przedmiot' => $przedmiot, 'luczniow' => $luczniow, 'iloscobecnosci' => $iloscobecnosci,
        			   'obecnosci' => $obecnosci, 'daty' => $daty, 'spisuczniow' => $spisuczniow);
        	$this->load->view("attendances_view", $tablica);
        	
		}
	}
}